﻿----- sp_GetDomainScopeUsersForMonitor -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDomainScopeUsersForMonitor]'))
DROP PROCEDURE [dbo].[sp_GetDomainScopeUsersForMonitor]
GO
CREATE PROCEDURE sp_GetDomainScopeUsersForMonitor(
	@DomainScopeKey [uniqueidentifier]
)
AS
BEGIN
	SELECT [Key],[PersonKey],[DomainScopeKey],[ThirdPartyIdentity],[Description],[MonitorStatus],[CreatedStamp],[LastUpdatedStamp]
	FROM dbo.[DomainScopeUser]
	WHERE [DomainScopeKey] = @DomainScopeKey;
END
GO


----- sp_GetMonitorClients -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetMonitorClients]'))
DROP PROCEDURE [dbo].[sp_GetMonitorClients]
GO
CREATE PROCEDURE sp_GetMonitorClients
AS
BEGIN
	SELECT [Key]
      ,[Name]
      ,[Description]
      ,[CreatedStamp]
      ,[LastUpdatedStamp]
      ,[LastSyncStamp]
  FROM [dbo].[MonitorClient]
END
GO

----- sp_GetMonitorClientByKey -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetMonitorClientByKey]'))
DROP PROCEDURE [dbo].[sp_GetMonitorClientByKey]
GO
CREATE PROCEDURE sp_GetMonitorClientByKey(
	@Key [uniqueidentifier]
)
AS
BEGIN
	SELECT [Key]
      ,[Name]
      ,[Description]
      ,[CreatedStamp]
      ,[LastUpdatedStamp]
      ,[LastSyncStamp]
	FROM [dbo].[MonitorClient]
	WHERE [Key]=@Key;
END
GO

----- sp_AddMonitorClient -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddMonitorClient]'))
DROP PROCEDURE [dbo].[sp_AddMonitorClient]
GO
CREATE PROCEDURE sp_AddMonitorClient(
	@Name [nvarchar](100),
	@Description [nvarchar](200),
	@Stamp [datetime]
)
AS
BEGIN
	DECLARE @Key AS [uniqueidentifier];
	
	IF(@Name IS NOT NULL)
	BEGIN
		SET @Key = NEWID();
		INSERT INTO [dbo].[MonitorClient]([key],[Name],[Description],[CreatedStamp], [LastUpdatedStamp])
			VALUES (@Key, @Name, @Description, @Stamp, @Stamp);
	END

	SELECT @Key;
END
GO

----- sp_UpdateMonitorClient -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateMonitorClient]'))
DROP PROCEDURE [dbo].[sp_UpdateMonitorClient]
GO
CREATE PROCEDURE sp_UpdateMonitorClient(
	@Key [uniqueidentifier],	
	@Description [nvarchar](200),
	@Stamp [datetime]
)
AS
BEGIN
	UPDATE [dbo].[MonitorClient]
	SET [Description] = @Description, [LastUpdatedStamp] = @Stamp
	WHERE [Key]=@Key;
END
GO

----- sp_QueryHeartbeat -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryHeartbeat]'))
DROP PROCEDURE [dbo].[sp_QueryHeartbeat]
GO
CREATE PROCEDURE sp_QueryHeartbeat(
	@ClientKey [uniqueidentifier],
	@TopCount [int]
)
AS
BEGIN
	DECLARE @SqlExp AS VARCHAR(MAX);
	DECLARE @WhereExp AS VARCHAR(MAX);

	IF ((@TopCount IS NULL) OR (@TopCount < 1))
		SET @TopCount = 200;

	SET @SqlExp = 'SELECT TOP ' + CONVERT(VARCHAR(10), @TopCount) + '  [Key]
      ,[Name]
      ,[ClientKey]
      ,[CreatedStamp]
	  FROM .[dbo].[ClientHeartbeatList]';
	SET @WhereExp = '';

	IF(@ClientKey IS NOT NULL)
		SET @WhereExp = ' WHERE [ClientKey] = '''+ CONVERT(VARCHAR(150), @ClientKey) + '''';
	
	EXEC( @SqlExp + @WhereExp + 'ORDER BY [CreatedStamp] DESC;');
END
GO

----- sp_CreateHeartbeat -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateHeartbeat]'))
DROP PROCEDURE [dbo].[sp_CreateHeartbeat]
GO
CREATE PROCEDURE sp_CreateHeartbeat(
	@ClientKey [uniqueidentifier],
	@LastSyncStamp [datetime],
	@Stamp [datetime]
)
AS
BEGIN
	DECLARE @Key AS [uniqueidentifier];
	DECLARE @SettingStamp AS [DATETIME];
	DECLARE @NeedRetrieveSetting AS [BIT];
	SET @SettingStamp = NULL;
	SET @NeedRetrieveSetting = 0;

	SELECT @Key=[Key],@SettingStamp = [LastSyncStamp]  FROM [dbo].[MonitorClient] WHERE [Key] = @ClientKey;
	IF(@Key IS NOT NULL)
	BEGIN
		INSERT INTO [dbo].[ClientHeartbeat] ([Key],[ClientKey],[CreatedStamp])
		VALUES(NEWID(), @ClientKey, @Stamp);

		IF(@LastSyncStamp IS NULL OR @LastSyncStamp < @SettingStamp)
			SET @NeedRetrieveSetting= 1;
	END
END
GO

----- sp_AddOrUpdateMonitorTask -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_AddOrUpdateMonitorTask]'))
DROP PROCEDURE [dbo].[sp_AddOrUpdateMonitorTask]
GO
CREATE PROCEDURE sp_AddOrUpdateMonitorTask(
	@Key [uniqueidentifier],
	@Name [nvarchar](100),
	@AssignedToClientKey [uniqueidentifier],
	@ExecutionType [int],
	@Options [int],
	@DomainScopeCode [varchar](20),
	@ThirdPartyIdentity [varchar](150),
	@IsNightOperationOnly [bit],
	@IsDisabled [bit],
	@Stamp [datetime]
)
AS
BEGIN
	IF (@Key IS NULL)
	BEGIN
		SET @Key = NEWID();	
		INSERT INTO [dbo].[MonitorTask] ([Key],[Name], [AssignedToClientKey],[ExecutionType],[Options],[DomainScopeCode],[ThirdPartyIdentity],[IsNightOperationOnly],[IsDisabled],[CreatedStamp],[LastUpdatedStamp],[LastExecutionStamp])
		VALUES (@Key, @Name, @AssignedToClientKey, @ExecutionType, @Options, @DomainScopeCode, @ThirdPartyIdentity, @IsNightOperationOnly, @IsDisabled, @Stamp, @Stamp , NULL);
	END
	ELSE
	BEGIN
		UPDATE [dbo].[MonitorTask]
		SET [Name]=@Name, [AssignedToClientKey]=@AssignedToClientKey, [ExecutionType]=@ExecutionType, [Options]=@Options, [IsNightOperationOnly]=@IsNightOperationOnly, [IsDisabled]=@IsDisabled, [LastUpdatedStamp]=@Stamp
		WHERE [Key]=@Key;
	END

	UPDATE [dbo].[MonitorClient]
	SET [LastSyncStamp]=@Stamp
	WHERE [Key]=@AssignedToClientKey;

	SELECT @Key;
END
GO

----- sp_GetMonitorTaskByClientKey -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetMonitorTaskByClientKey]'))
DROP PROCEDURE [dbo].[sp_GetMonitorTaskByClientKey]
GO
CREATE PROCEDURE sp_GetMonitorTaskByClientKey(
	@ClientKey [uniqueidentifier],
	@IncludeDisabled [bit]
)
AS
BEGIN
	IF(@IncludeDisabled = 1)
		SELECT [Key]
		  ,[Name]
		  ,[AssignedToClientKey]
		  ,[ExecutionType]
		  ,[Options]
		  ,[DomainScopeCode]
		  ,[ThirdPartyIdentity]
		  ,[IsNightOperationOnly]
		  ,[IsDisabled]
		  ,[CreatedStamp]
		  ,[LastUpdatedStamp]
		  ,[LastExecutionStamp]
		FROM [dbo].[MonitorTask]
		WHERE [AssignedToClientKey]=@ClientKey
		ORDER BY [CreatedStamp];
	ELSE
		SELECT [Key]
		  ,[Name]
		  ,[AssignedToClientKey]
		  ,[ExecutionType]
		  ,[Options]
		  ,[DomainScopeCode]
		  ,[ThirdPartyIdentity]
		  ,[IsNightOperationOnly]
		  ,[IsDisabled]
		  ,[CreatedStamp]
		  ,[LastUpdatedStamp]
		  ,[LastExecutionStamp]
		FROM [dbo].[MonitorTask]
		WHERE [AssignedToClientKey]=@ClientKey AND [IsDisabled] = 0
		ORDER BY [CreatedStamp];
END
GO

----- sp_GetMonitorTaskByKey -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetMonitorTaskByKey]'))
DROP PROCEDURE [dbo].[sp_GetMonitorTaskByKey]
GO
CREATE PROCEDURE sp_GetMonitorTaskByKey(
	@Key [uniqueidentifier]
)
AS
BEGIN
		SELECT [Key]
		  ,[Name]
		  ,[AssignedToClientKey]
		  ,[ExecutionType]
		  ,[Options]
		  ,[DomainScopeCode]
		  ,[ThirdPartyIdentity]
		  ,[IsNightOperationOnly]
		  ,[IsDisabled]
		  ,[CreatedStamp]
		  ,[LastUpdatedStamp]
		  ,[LastExecutionStamp]
		FROM [dbo].[MonitorTask]
		WHERE [Key]=@Key;
	
END
GO

----- sp_QueryMonitorTasks -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryMonitorTasks]'))
DROP PROCEDURE [dbo].[sp_QueryMonitorTasks]
GO
CREATE PROCEDURE sp_QueryMonitorTasks(
	@Name [nvarchar](100),
	@AssignedToClientKey [uniqueidentifier],
	@ExecutionType [int],
	@ThirdPartyIdentity [varchar](150),
	@IsNightOperationOnly [bit],
	@IsDisabled [bit]
)
AS
DECLARE @SqlExp AS nvarchar(Max);
DECLARE @WhereExp AS nvarchar(Max);
BEGIN
	SET @SqlExp = 'SELECT [Key]
		  ,[Name]
		  ,[AssignedToClientKey]
		  ,[ExecutionType]
		  ,[DomainScopeCode]
		  ,[ThirdPartyIdentity]
		  ,[IsNightOperationOnly]
		  ,[IsDisabled]
		  ,[CreatedStamp]
		  ,[LastUpdatedStamp]
		  ,[LastExecutionStamp]';	
	SET @WhereExp = '1=1';
	IF(@Name IS NOT NULL)
		SET @WhereExp = ' AND [Name] = '''+ CONVERT(nvarchar(100),@Name) + '''';	
	IF(@AssignedToClientKey IS NOT NULL)
		SET @WhereExp = ' AND [AssignedToClientKey] = '''+ CONVERT(varchar(150),@AssignedToClientKey) + '''';
	IF(@ExecutionType IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [ExecutionType] = ' + CONVERT(varchar(20),@ExecutionType);
	IF(@ThirdPartyIdentity IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [ThirdPartyIdentity] LIKE ''%%' + dbo.ReformatLikeStatement(@ThirdPartyIdentity) + '''';
	IF(@IsNightOperationOnly IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [IsNightOperationOnly] = ' + CONVERT(varchar(5),@IsNightOperationOnly);
	IF(@IsDisabled IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [IsDisabled] = ' + CONVERT(varchar(5),@IsDisabled);

	IF(LEN(@WhereExp) = 3)
		SET @WhereExp='';
	ELSE
		SET @WhereExp=' WHERE ' + @WhereExp;
	EXEC(@SqlExp + @WhereExp);
		
END
GO

/*
----- sp_CreateTaskExecutionRecord -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreateTaskExecutionRecord]'))
DROP PROCEDURE [dbo].[sp_CreateTaskExecutionRecord]
GO
CREATE PROCEDURE sp_CreateTaskExecutionRecord(
	@TaskKey [uniqueidentifier],
	@IsSuccess	[bit],
	@Exception [nvarchar](MAX),
	@Stamp [datetime]
)
AS
BEGIN
		INSERT INTO [dbo].[TaskExecutionRecord] ([TaskKey],[IsSuccess],[Exception],[CreatedStamp])
		VALUES(@TaskKey, @IsSuccess, @Exception, @Stamp);

		UPDATE [dbo].[MonitorTask]
		SET LastExecutionStamp = @Stamp
		WHERE [Key] = @TaskKey;
END
GO

----- sp_QueryTaskExecutionRecord -----
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryTaskExecutionRecord]'))
DROP PROCEDURE [dbo].[sp_QueryTaskExecutionRecord]
GO
CREATE PROCEDURE sp_QueryTaskExecutionRecord(
	@TaskKey [uniqueidentifier],
	@IsSuccess	[bit],
	@StartStamp [datetime],
	@EndStamp [datetime]
)
AS
DECLARE @SqlExp AS nvarchar(Max);
DECLARE @WhereExp AS nvarchar(Max);
BEGIN
	SET @SqlExp = 'SELECT TOP 500 [RecordKey]
		,[TaskKey]
		,[IsSuccess]
		,[Exception]
		,[RecordCreatedStamp]
		,[TaskExecutionType]
		,[TaskName]
		,[DomainScopeCode]
		,[DomainScopeName]
		,[TaskThirdPartyIdentity]
		,[ClientName]
	FROM [dbo].[TaskExecutionRecordList]';

	SET @WhereExp = '1=1';
	IF(@TaskKey IS NOT NULL)
		SET @WhereExp = ' AND [TaskKey] = '''+ CONVERT(nvarchar(100),@TaskKey) + '''';	
	IF(@IsSuccess IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [IsSuccess] = ' + CONVERT(varchar(5),@IsSuccess);
	IF(@StartStamp IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [RecordCreatedStamp] >= ''' + CONVERT(varchar(50),@StartStamp)+'''';
	IF(@EndStamp IS NOT NULL)
		SET @WhereExp = @WhereExp + ' AND [RecordCreatedStamp] < ''' + CONVERT(varchar(50),@EndStamp)+'''';

	IF(LEN(@WhereExp) = 3)
		SET @WhereExp='';
	ELSE
		SET @WhereExp=' WHERE ' + @WhereExp;

	EXEC(@SqlExp + @WhereExp);
		
END
GO
*/